/**
 * @ClassName: UserOrderDao
 * @author linsen
 * @date 2016-10-23
 */
package com.fzrskj.cloud.free.user.dao;

import com.fzrskj.cloud.free.core.tool.CObjectTool;
import com.fzrskj.cloud.free.core.utils.AgentDataRangeUtils;
import com.fzrskj.cloud.free.core.utils.CloudContextUtils;
import com.fzrskj.cloud.free.core.utils.CloudDbDrvUtils;
import com.fzrskj.cloud.free.core.utils.DataRangeUtils;
import com.fzrskj.cloud.free.user.bean.UserOrder;
import com.fzrskj.cloud.free.user.bean.UserOrderView;
import com.fzrskj.cloud.free.user.bean.statistics.Statistics;
import com.fzrskj.cloud.free.user.tool.StatistisTool;
import ms.core.consts.SysErr;
import ms.core.db.IDbDrv;
import ms.core.object.CObject;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class UserOrderDao extends CObject {
    //private declarations
    private IDbDrv db = null;

    //constructor function to initialize database access object
    public UserOrderDao() {
        db = CloudDbDrvUtils.getDbUtilsDrv();
    }

    //to free resource
    @Override
    public void dispose() {
        db.closeConnection();
    }

    //to add new record to database by bean
    public int addUserOrder(UserOrder obj) {
        //if (obj.getAgentId() == null){
        obj.setAgentId(CloudContextUtils.getAgentId());
        //}
        String sql = "insert into user_order(applyTime,couponId,deductCash,handleStatus,integral," +
                "orderDate,orderId,orderParam,orderType,payAmount,payStatus," +
                "priceType,productClass,productCode,regType,serviceId,totalAmount," +
                "userId,userName,cartNo,agentId)" +
                "values(?,?,?,?,?," +
                "?,?,?,?,?,?," +
                "?,?,?,?,?,?," +
                "?,?,?,?);";
        Object[] prm = {obj.getApplyTime(), obj.getCouponId(), obj.getDeductCash(), obj.getHandleStatus(), obj.getIntegral(),
                obj.getOrderDate(), obj.getOrderId(), obj.getOrderParam(), obj.getOrderType(), obj.getPayAmount(), obj.getPayStatus(),
                obj.getPriceType(), obj.getProductClass(), obj.getProductCode(), obj.getRegType(), obj.getServiceId(), obj.getTotalAmount(),
                obj.getUserId(), obj.getUserName(), obj.getCartNo(), obj.getAgentId()};
        int ret = db.executeSql(sql, prm);
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        return ret;
    }

    /**
     * 查询到期未支付订单
     *
     * @param dateTime
     * @return
     */
    public List<UserOrder> queryExpireNoPayOrder(String dateTime) {
        String sql = "select * from user_order where Date(orderDate)<=Date(?) and payStatus='N' ";
        Object[] params = {dateTime};
        List<UserOrder> ret = db.queryForBeans(sql, UserOrder.class, params);
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        return ret;
    }

    /**
     * 删除订单信息
     *
     * @param id
     * @return
     */
    public int delUserOrder(int id) {
        String sql = "delete from user_order where id=? and agentId=?";
        int ret = db.executeSql(sql, new Object[]{id, CloudContextUtils.getAgentId()});
        String code = db.getErrCode();
        this.setErrCode(code);
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        return ret;
    }

    /**
     * 根据orderId删除数据
     *
     * @param orderId
     * @return
     */
    public int delUserOrder(String orderId) {
        String sql = "delete from user_order where orderId=? and agentId=?";
        int ret = db.executeSql(sql, new Object[]{orderId, CloudContextUtils.getAgentId()});
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        return ret;
    }

    //to delete table record by cdt
    public int delUserOrder(Map<String, Object> cdt) {
        //if (!cdt.containsKey("agentId")) {
        CObjectTool.clearMapAgentId(cdt);
        cdt.put("agentId", CloudContextUtils.getAgentId());
        //}
        int ret = db.deleteRecord("user_order", cdt);
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        return ret;
    }

    public int modUserOrder(Map<String, Object> values, Map<String, Object> cdt) {
        //if (!cdt.containsKey("agentId")) {
        CObjectTool.clearMapAgentId(cdt);
        cdt.put("agentId", CloudContextUtils.getAgentId());
        //}
        int ret = db.modifyRecord("user_order", values, cdt);
        String code = db.getErrCode();
        this.setErrCode(code);
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }

        return ret;
    }

    /**
     * 获取订单详情视图
     *
     * @param orderId
     * @return
     */
    public UserOrderView getUserOrderView(String orderId) {
        StringBuilder sb = new StringBuilder("select uo.*,sp.productName,");
        sb.append("(select t.description from dic_paramindex n,dic_paramitem t where t.paramId=n.paramEName  and n.paramEName='productclass' and t.value= uo.productClass) as productClassName,");
        sb.append("(select t.description from dic_paramindex n,dic_paramitem t where t.paramId=n.paramEName  and n.paramEName='orderType' and t.value= uo.orderType) as orderTypeName,");
        sb.append("(select t.description from dic_paramindex n,dic_paramitem t where t.paramId=n.paramEName  and n.paramEName='payStatus' and t.value= uo.payStatus) as payStatusName,");
        sb.append("(select t.description from dic_paramindex n,dic_paramitem t where t.paramId=n.paramEName  and n.paramEName='handleStatus' and t.value= uo.handleStatus) as handleStatusName ");
        sb.append(" from user_order uo,sys_product sp where uo.productCode=sp.productCode and orderId=?  and uo.agentId=?");
        UserOrderView list = db.queryForBean(sb.toString(), UserOrderView.class, new Object[]{orderId, CloudContextUtils.getAgentId()});
        String code = db.getErrCode();
        this.setErrCode(code);
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(code);
        }
        return list;
    }

    /**
     * 获取订单详情
     *
     * @param orderId
     * @return
     */
    public UserOrder getUserOrder(String orderId) {
        StringBuilder sb = new StringBuilder("select t.* from user_order t where 1=1 and orderId=? and agentId=?");
        UserOrder list = db.queryForBean(sb.toString(), UserOrder.class, new Object[]{orderId, CloudContextUtils.getAgentId()});
        String code = db.getErrCode();
        this.setErrCode(code);
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(code);
        }
        return list;
    }

    /**
     * agent user.
     * @param orderId
     * @return
     */
    public UserOrder getUserOrderWithoutAgent(String orderId) {
        StringBuilder sb = new StringBuilder("select t.* from user_order t where 1=1 and orderId=? ");
        UserOrder list = db.queryForBean(sb.toString(), UserOrder.class, new Object[]{orderId});
        String code = db.getErrCode();
        this.setErrCode(code);
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(code);
        }
        return list;
    }

    /**
     * 查询删除订单
     *
     * @param serviceId
     * @return
     */
    public UserOrder queryDelOrder(String serviceId) {
        StringBuilder sb = new StringBuilder("select t.* from user_order t where 1=1 and serviceId=? and orderType='delete' and agentId=?");
        UserOrder ret = db.queryForBean(sb.toString(), UserOrder.class, new Object[]{serviceId, CloudContextUtils.getAgentId()});
        String code = db.getErrCode();
        this.setErrCode(code);
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(code);
        }
        return ret;
    }

    /**
     * 统计订单数
     *
     * @param cdt
     * @return
     */
    public int queryUserOrderCount(Map<String, Object> cdt) {
        //if (!cdt.containsKey("agentId")) {
       // CObjectTool.clearMapAgentId(cdt);
        //cdt.put("agentId", CloudContextUtils.getAgentId());
        //}
        List<Object> list = new ArrayList<>();
        StringBuilder sb = new StringBuilder("select count(id) from user_order where 1=1 ");
        for (Map.Entry<String, Object> c : cdt.entrySet()) {
            if (c.getKey().equals("start")) {
                sb.append(" and orderDate >=?");
                list.add(c.getValue());
            } else if (c.getKey().equals("end")) {
                sb.append(" and orderDate <?");
                list.add(c.getValue());
            } else if ("remark".equals(c.getKey())) {
                sb.append(" and remark like ? ");
                list.add("%" + c.getValue().toString() + "%");
            } else {
                sb.append(" and " + c.getKey() + "=?");
                list.add(c.getValue());
            }
        }

        //代理数据隔离
        AgentDataRangeUtils.formatDataRange(CloudContextUtils.getAgentId(), sb);

        //数据隔离
        DataRangeUtils.formatRangeSql(sb, " userId ", list);

        Object[] params = new Object[list.size()];
        for (int i = 0; i < list.size(); i++) {
            params[i] = list.get(i);
        }

        Long ret = (Long) db.queryFieldValue(sb.toString(), Long.class, params, 1);
        this.setErrCode(db.getErrCode());
        return ret.intValue();
    }

    /**
     * 查询订单列表视图
     *
     * @param cdt
     * @param page
     * @param pagesize
     * @return
     */
    public List<UserOrderView> queryUserOrderViewList(Map<String, Object> cdt, int page, int pagesize) {
        //if (!cdt.containsKey("agentId")) {
        CObjectTool.clearMapAgentId(cdt);
        cdt.put("uo.agentId", CloudContextUtils.getAgentId());
        //}
        StringBuilder sb = new StringBuilder("select uo.*,");
        sb.append("(select productName from sys_product sp where sp.productCode=uo.productCode ");

        sb.append(" and sp.agentId='" + CloudContextUtils.getAgentId()+"'");

        sb.append(") as productName,");
        sb.append("(select t.description from dic_paramindex n,dic_paramitem t where t.paramId=n.paramEName  and n.paramEName='productclass' and t.value= uo.productClass) as productClassName,");
        sb.append("(select t.description from dic_paramindex n,dic_paramitem t where t.paramId=n.paramEName  and n.paramEName='orderType' and t.value= uo.orderType) as orderTypeName,");
        sb.append("(select t.description from dic_paramindex n,dic_paramitem t where t.paramId=n.paramEName  and n.paramEName='payStatus' and t.value= uo.payStatus) as payStatusName,");
        sb.append("(select t.description from dic_paramindex n,dic_paramitem t where t.paramId=n.paramEName  and n.paramEName='handleStatus' and t.value= uo.handleStatus) as handleStatusName ");
        sb.append(" from user_order uo where 1=1 ");

        List<Object> list = new ArrayList<>();
        for (Map.Entry<String, Object> c : cdt.entrySet()) {
            if (c.getKey().equals("start")) {
                sb.append(" and orderDate >=?");
                list.add(c.getValue());
            } else if (c.getKey().equals("end")) {
                sb.append(" and orderDate <?");
                list.add(c.getValue());
            } else if (c.getKey().equals("orderParam")) {
                sb.append(" and LOCATE(?,orderParam)>0");
                list.add(c.getValue());
            } else if ("remark".equals(c.getKey())) {
                sb.append(" and remark like ? ");
                list.add("%" + c.getValue().toString() + "%");
            } else {
                sb.append(" and " + c.getKey() + "=?");
                list.add(c.getValue());
            }
        }

        //数据隔离
        DataRangeUtils.formatRangeSql(sb, " uo.userId ", list);

        sb.append(" order by orderDate desc");
        if ((page > 0) && (pagesize > 0)) {
            sb.append(String.format(" limit %d,%d", (page - 1) * pagesize, pagesize));
        }
        Object[] params = new Object[list.size()];
        for (int i = 0; i < list.size(); i++) {
            params[i] = list.get(i);
        }
        List<UserOrderView> ret = db.queryForBeans(sb.toString(), UserOrderView.class, params);
        this.setErrCode(db.getErrCode());
        return ret;
    }

    /**
     * 查询订单
     *
     * @param userId
     * @param orderIds
     * @return
     */
    public List<UserOrderView> getUserOrderList(int userId, List<String> orderIds) {
        List<UserOrderView> list = new ArrayList<>();
        for (String str : orderIds) {
            StringBuilder sb = new StringBuilder("select uo.*,");
            sb.append("(select productName from sys_product sp where sp.productCode=uo.productCode and agentId=?) as productName,");
            sb.append("(select t.description from dic_paramindex n,dic_paramitem t where t.paramId=n.paramEName  and n.paramEName='productclass' and t.value= uo.productClass and agentId=?) as productClassName,");
            sb.append("(select t.description from dic_paramindex n,dic_paramitem t where t.paramId=n.paramEName  and n.paramEName='orderType' and t.value= uo.orderType and agentId=?) as orderTypeName,");
            sb.append("(select t.description from dic_paramindex n,dic_paramitem t where t.paramId=n.paramEName  and n.paramEName='payStatus' and t.value= uo.payStatus and agentId=?) as payStatusName,");
            sb.append("(select t.description from dic_paramindex n,dic_paramitem t where t.paramId=n.paramEName  and n.paramEName='handleStatus' and t.value= uo.handleStatus and agentId=?) as handleStatusName ");
            sb.append(" from user_order uo where orderId=? and uo.agentId=? ");

            List<Object> param = new ArrayList<>();
            param.add(CloudContextUtils.getAgentId());
            param.add(CloudContextUtils.getAgentId());
            param.add(CloudContextUtils.getAgentId());
            param.add(CloudContextUtils.getAgentId());
            param.add(CloudContextUtils.getAgentId());
            param.add(str);
            param.add(CloudContextUtils.getAgentId());
            if (userId > 0) {
                sb.append("and userId=?");
                param.add(userId);
            }

            Object[] params = new Object[param.size()];
            for (int i = 0; i < param.size(); i++) {
                params[i] = param.get(i);
            }
            UserOrderView obj = db.queryForBean(sb.toString(), UserOrderView.class, params);
            this.setErrCode(db.getErrCode());
            list.add(obj);
        }
        return list;
    }

    /**
     * 查询符合条件的订单数
     *
     * @param cdt
     * @return
     */
    public int queryOrderExistCount(Map<String, Object> cdt) {
        //if (!cdt.containsKey("agentId")) {
        CObjectTool.clearMapAgentId(cdt);
        cdt.put("agentId", CloudContextUtils.getAgentId());
        //}
        List<Object> list = new ArrayList<>();
        StringBuilder sb = new StringBuilder("select count(id) from user_order where 1=1 ");
        for (Map.Entry<String, Object> c : cdt.entrySet()) {
            if (c.getKey().equals("instanceId")) {
                sb.append(" and LOCATE(?,orderParam)>0");
                list.add(c.getValue());
            } else if (c.getKey().equals("diskId")) {
                sb.append(" and LOCATE(?,orderParam)>0");
                list.add(c.getValue());
            } else if (c.getKey().equals("inStatus")) {
                sb.append(" and handleStatus in ('").append(c.getValue().toString().replaceAll(",", "', '")).append("')");
            } else {
                sb.append(" and " + c.getKey() + "=?");
                list.add(c.getValue());
            }
        }

        Object[] params = new Object[list.size()];
        for (int i = 0; i < list.size(); i++) {
            params[i] = list.get(i);
        }

        Long ret = (Long) db.queryFieldValue(sb.toString(), Long.class, params, 1);
        this.setErrCode(db.getErrCode());
        return ret.intValue();
    }

    /**
     * 按时间段统计符合条件的订单数
     *
     * @param cdt
     * @param qryType H 小时 D 天 M 月 Y 年
     * @return add wzj by 2018/12/12
     */
    public List<Statistics> queryOrderCount(Map<String, Object> cdt, String qryType) {
        //if (!cdt.containsKey("agentId")) {
        CObjectTool.clearMapAgentId(cdt);
        cdt.put("agentId", CloudContextUtils.getAgentId());
        //}
        String format = StatistisTool.getSqlDateFormat(qryType);
        List<Object> list = new ArrayList<>();
        StringBuilder sb = new StringBuilder("select ifnull(count(id),0) as count, DATE_FORMAT(orderDate, '" + format + "') as dateTime from user_order where 1=1 ");
        for (Map.Entry<String, Object> c : cdt.entrySet()) {
            if (c.getKey().equals("startTime")) {
                sb.append(" and orderDate >= ?");
                list.add(c.getValue());
            } else if (c.getKey().equals("endTime")) {
                sb.append(" and orderDate <= ?");
                list.add(c.getValue());
            } else {
                sb.append(" and " + c.getKey() + "=?");
                list.add(c.getValue());
            }
        }

        sb.append(" group by DATE_FORMAT(orderDate,'" + format + "') ");
        sb.append(" order by orderDate asc");
        Object[] params = new Object[list.size()];
        for (int i = 0; i < list.size(); i++) {
            params[i] = list.get(i);
        }
        List<Statistics> rets = db.queryForBeans(sb.toString(), Statistics.class, params);
        this.setErrCode(db.getErrCode());
        return rets;
    }

}